package com.dao.zk;

import com.bean.zk.Emp;
import com.util.zk.Dbutil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Repository
public class EmpDAO {

    /**
     * 根据用户名密码查询
     * @param num
     * @param pass
     * @return
     */
    public Map<String,Object>  select(String num, String pass){
        Connection con = null;
        QueryRunner query = new QueryRunner();
        Map<String,Object>  emp =null;
        try {
            con = Dbutil.getConnection();
            String sql = "select * from emp where num = ? and pass = ? and status =1";
            Object[] args = new Object[]{num, pass};
            emp = query.query(con, sql, new MapHandler(), args);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
    return  emp;
    }


    /**
     * 查询所有emp
     * @return
     */
    public List<Map<String, Object>> getEmps(){

        Connection con = null;
        QueryRunner query = new QueryRunner();
        List<Map<String, Object>> emp =null;
        try {
            con = Dbutil.getConnection();
            String sql = "select e.eid ,e.ename,d.dname,s.sname,e.remark,e.openid,e.phone,e.email,e.cardid,e.sex " +
                    " from emp e ,dept d,state s" +
                    " where e.did = d.did and e.`status` =s.sid";
            emp = query.query(con, sql, new MapListHandler());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return  emp;
    }

    /**
     * 根据员工名称和部门模糊查寻
     */
    public List<Map<String, Object>> getEmpBlur(String ename,int did){
        Connection con = null;
        QueryRunner query = new QueryRunner();
        List<Map<String, Object>> emp =null;
        try {
            con = Dbutil.getConnection();
            String sql = "select e.eid ,e.ename,d.dname,s.sname,e.remark,e.openid,e.phone,e.email,e.cardid,e.sex " +
                    "from emp e,dept d,state s where e.did = d.did and e.`status` =s.sid and e.ename like ? and d.did = ?;";
            Object[] args = new Object[]{"%"+ename+"%",did};
            emp = query.query(con, sql, new MapListHandler(),args);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return  emp;
    }

    /**
     * 根据eid查询
     */
    public Map<String, Object> getEmpByEid(int eid){
        Connection con = null;
        QueryRunner query = new QueryRunner();
        Map<String, Object> emp =null;
        try {
            con = Dbutil.getConnection();
            String sql = "select * from emp where eid = ?;";
            emp = query.query(con, sql, new MapHandler(),eid);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return emp;
    }
    /**
     * 根据rid查询
     */
    public Map<String, Object> getEmpByRid(int rid){
        Connection con = null;
        QueryRunner query = new QueryRunner();
        Map<String, Object> emp =null;
        try {
            con = Dbutil.getConnection();
            String sql = "select * from emp_role where rid = ?;";
            emp = query.query(con, sql, new MapHandler(),rid);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return emp;
    }

    /**
     * 添加员工
     * @return
     */
    public int insert(Emp emp){
        Connection con = null;
        QueryRunner query = new QueryRunner();
        int update = 0;
        try {
            con = Dbutil.getConnection();
            String sql = "insert into  emp values(null,?,?,?,?,?,?,?,?,?,?,?)";
            Object[] args = new Object[]{emp.getEname(),emp.getDid(),emp.getStatus(),emp.getRemark()
            ,emp.getOpenid(), emp.getPhone(),emp.getNum(),emp.getPass(),emp.getCardid(),emp.getSex(),emp.getEmail()};
            update= query.update(con, sql, args);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return  update;
    }


    /**
     * 删除根据id
     * @param eid
     * @return
     */
    public int delete(int eid){
        Connection con = null;
        QueryRunner query = new QueryRunner();
        int delete = 0;
        try {
            con = Dbutil.getConnection();
            String sql = "delete from emp  where eid = ?";
            delete= query.update(con, sql,eid);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return  delete;
    }

    public int update(int eid ,Emp emp){
        Connection con = null;
        QueryRunner query = new QueryRunner();
        int update = 0;
        try {
            con = Dbutil.getConnection();
            String sql = "update emp set ename = ? ,did = ?,status = ?,remark = ?,openid = ?,phone = ?, num= ?,pass = ?," +
                    "cardid = ?,sex = ?,email = ? where eid = ?";
            Object[] args = new Object[]{emp.getEname(),emp.getDid(),emp.getStatus(),emp.getRemark()
                    ,emp.getOpenid(), emp.getPhone(),emp.getNum(),emp.getPass(),emp.getCardid(),emp.getSex(),emp.getEmail(),eid};
            update= query.update(con, sql,args);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return  update;
    }


    public Map<String, Object> selectByPhone(Emp emp) {
        Connection con = null;
        QueryRunner query = new QueryRunner();
        Map<String, Object> emplist = null;
        try {
            con = Dbutil.getConnection();
            String sql = "select * from emp where phone = ? and status =1;";
            emplist = query.query(con, sql, new MapHandler(),emp.getPhone());
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return emplist;
    }

    public int updatePswd(Emp emp) {
        Connection con = null;
        QueryRunner query = new QueryRunner();
        int update = 0;
        try {
            con = Dbutil.getConnection();
            String sql = "update emp set pass = ? where phone = ?";
            Object[] args = new Object[]{emp.getPass(),emp.getPhone()};
            update= query.update(con, sql,args);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            Dbutil.closeAll(null,null,con);
        }
        return  update;
    }
}
